package com.pic.mgt.dao.syushin;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Collections;
import java.util.Date;
import java.util.Map;
import java.util.Vector;

import org.springframework.dao.DataAccessException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;

import com.pic.mgt.dao.BasicDao;
import com.pic.mgt.utils.IgxCollectionUtils;
import com.pic.mgt.utils.SqlScriptUtils;

public class SyushinBucketMgtDao extends BasicDao implements ISyushinBucketMgtDao {
	private final String tableName = "syushin_bucket_mgt"; 
	private final String insertBasicSqlScript;
	private final String updateBucketTimeAndBatchFileAmountByBucketFilenameSqlScript;
	private final String updateCreatingTimeByBucketFilenameSqlScript;
	private final String findSyushinBucketMgtInfoByBucketFilenameSqlScript;
	
	public SyushinBucketMgtDao() {
		super();
		
		// 建立 Insert Basic 的 Sql Scritp
		insertBasicSqlScript = createInsertBasicSQLScript();
		
		// 建立 update ubkcet time by bucket filename 的 Sql scritp
		updateBucketTimeAndBatchFileAmountByBucketFilenameSqlScript = createUpdateBucketTimeAndBatchFileAmountByBucketFilenameSqlScript();
		
		// 建立 update creating time by bucket filename 的 Sql Script
		this.updateCreatingTimeByBucketFilenameSqlScript = createUpdateCreatingTimeByBucketFilenameSqlScript();
		
		// 建立 find syushin bucket mgt info by bucket filename 的 Sql Script
		this.findSyushinBucketMgtInfoByBucketFilenameSqlScript = createFindSyushinBucketMgtInfoByBucketFilenameSqlScript();
	}
	
	private String createUpdateCreatingTimeByBucketFilenameSqlScript() {
		Vector<String> updateFieldsVector = new Vector<String>();
		
		updateFieldsVector.add(_creatingTime);
		
		return SqlScriptUtils.createNamedParamUpdateScript(
										tableName,
										updateFieldsVector,
										_bucketFilename);
	}
	
	private String createFindSyushinBucketMgtInfoByBucketFilenameSqlScript() {
		Vector<String> selectFieldsVector =
									new Vector<String>();

		selectFieldsVector.add(_batchFileAmount);
		selectFieldsVector.add(_bucketFilename);
		selectFieldsVector.add(_bucketNo);
		selectFieldsVector.add(_bucketTime);
		selectFieldsVector.add(_businessName);
		selectFieldsVector.add(_creatingTime);

		return SqlScriptUtils.createNamedParamQueryScriptWithSingleCondition(
				tableName, selectFieldsVector, _bucketFilename);
	}
	
	private String createUpdateBucketTimeAndBatchFileAmountByBucketFilenameSqlScript() {
		Vector<String> updateFieldsVector = new Vector<String>();
		
		updateFieldsVector.add(_bucketTime);
		updateFieldsVector.add(_batchFileAmount);
		
		return SqlScriptUtils.createNamedParamUpdateScript(
										tableName,
										updateFieldsVector,
										_bucketFilename);
	}
	
	private String createInsertBasicSQLScript() {
		Vector<String> insertFieldsVector =
							new Vector<String>();
		
		// 要新增的欄位
		insertFieldsVector.add(_bucketFilename);
		insertFieldsVector.add(_bucketTime);
		insertFieldsVector.add(_businessName);
		insertFieldsVector.add(_bucketNo);
		insertFieldsVector.add(_batchFileAmount);
		
		return SqlScriptUtils.createNamedParamInsertScript(this.tableName,
											     insertFieldsVector);
	}
	
	@Override
	public int updateCreatingTimeByBucketFilename(Date creatingTime,
			String bucketFilename) throws DataAccessException {
		if (null != creatingTime && null != bucketFilename) {
			Map<String, Object> updateValuesMap =
				IgxCollectionUtils.convertToMap(
						new Object[] {_creatingTime, creatingTime,
									  "__ConditionValue", bucketFilename});
		
		return
			super.namedParameterJdbcTemplate.update(
							this.updateCreatingTimeByBucketFilenameSqlScript,
							updateValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public SyushinBucketMgtInfoBean findSyushinBucketMgtInfoByBucketFilename(
			String bucketFilename) throws DataAccessException {
		try {
			return
			super.namedParameterJdbcTemplate.queryForObject(
					this.findSyushinBucketMgtInfoByBucketFilenameSqlScript,
					Collections.singletonMap((String)_bucketFilename, (Object)bucketFilename),
					new RowMapper<SyushinBucketMgtInfoBean>() {
						public SyushinBucketMgtInfoBean mapRow(ResultSet rs, int rowNum) throws SQLException {
							SyushinBucketMgtInfoBean syushinBucketMgtInfoBean = new SyushinBucketMgtInfoBean();
						
							syushinBucketMgtInfoBean.setBatchFileAmount(rs.getInt(_batchFileAmount));
							syushinBucketMgtInfoBean.setBucketFilename(rs.getString(_bucketFilename));
							syushinBucketMgtInfoBean.setBucketNo(rs.getInt(_bucketNo));
							syushinBucketMgtInfoBean.setBusinessName(rs.getString(_businessName));
							syushinBucketMgtInfoBean.setCreatingTime(new Date(rs.getTimestamp(_creatingTime).getTime()));
							
							Timestamp bucketTimestamp = rs.getTimestamp(_bucketTime);
							if (null != bucketTimestamp) {
								syushinBucketMgtInfoBean.setBucketTime(new Date(bucketTimestamp.getTime()));
							}
							else {
								syushinBucketMgtInfoBean.setBucketTime(null);
							}
							
							return syushinBucketMgtInfoBean;
						}
					}
				);
		} catch (EmptyResultDataAccessException e) {
			return null;
		} catch (DataAccessException e) {
			throw e;
		}
	}
	
	@Override
	public int updateBucketTimeAndBatchFileAmountByBucketFilename(
			Timestamp bucketTime, int batchFileAmount, String bucketFilename)
			throws DataAccessException {
		if (null != bucketTime && null != bucketFilename) {
			Map<String, Object> updateValuesMap =
				IgxCollectionUtils.convertToMap(
						new Object[] {_bucketTime, bucketTime,
									  _batchFileAmount, batchFileAmount,
									  "__ConditionValue", bucketFilename});
		
		return
			super.namedParameterJdbcTemplate.update(
							this.updateBucketTimeAndBatchFileAmountByBucketFilenameSqlScript,
							updateValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public int insertBasic(Map<String, Object> insertValuesMap)
			throws DataAccessException {
		if (null != insertValuesMap && !insertValuesMap.isEmpty()) {
			return
				super.namedParameterJdbcTemplate.update(this.insertBasicSqlScript,
														insertValuesMap);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public String getTableName() {
		return tableName;
	}
	
}
